package com.info.zhiduoduo.repository.mapper.company;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.mapper.Condition;
import com.info.zhiduoduo.repository.dto.company.*;
import com.info.zhiduoduo.repository.entity.company.CompanyAccount;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 企业账户 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2018-12-23
 */
public interface CompanyAccountMapper extends BaseMapper<CompanyAccount> {

	default CompanyAccount getByCompid(int compid) {
		return new CompanyAccount().selectOne(Condition.create().eq("compid", compid));
	}

	@Select(" select ca.acctid,ca.merchantid,ca.md5key,ca.merchant_request_seq,ca.verify_phone," +
			" ca.maker_groupon,ca.business_manager_id,bm.business_manager,bm.phone as businessManagerPhone," +
			" ca.marketing_cooperative,ca.marketing_cooperative_qrcode," +
			" ca.economy_category_id,ca.core_module,ca.today_amount_reminder,ca.strategy_invoice,ca.show_zhaoshang_ledger," +
			" c.compid,c.compname,c.regphone,c.creditcode,c.contactname,c.contactphone,c.compaddress,c.telephone," +
			" c.acctbank,c.acctno,c.complicensephoto,c.invoice_scope," +
			" c.deposit_flag,c.deposit_rate,c.return_period,c.secondary_monthly_amount,c.secondary_deposit_rate,c.special_rate_flag, " +
			" c.crowdsource_partner_id,pa.realname as crowdsource_partner_name,ca.whether_subject,c.quota_flag," +
			" c.compemail,c.contactaddress " +
			" from zdd_company_account ca " +
			" join zdd_company c on c.compid = ca.compid " +
			" left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
			" left join zdd_partner_account pa on pa.partnerid = c.crowdsource_partner_id " +
			" where ca.acctid = #{acctid}")
	AccountDetail getAccountDetail(@Param("acctid") Integer acctid);

	/**
	 * 查询企业余额，企业运营主体，根据企业支付方式与运营主体映射表查询，线下支付2和4的查询企业绑定的运营主体
	 */
    @Select("SELECT c.compid,c.compname," +
            " SUM(bltBalance + zsBalance) AS bankBalance," +
            " SUM(makerGrouponBalance) AS makerGrouponBalance, " +
            " SUM(bltBalance + zsBalance + makerGrouponBalance) as total " +
			" FROM zdd_company c " +
            " LEFT JOIN (SELECT compid," +
            "			SUM(IF(payment.source_table is null, pay.balance,0)) AS bltBalance," +
            "			SUM(IF(payment.source_table = 1, pay.balance,0)) AS zsBalance," +
            "			SUM(IF(payment.source_table = 2, pay.balance,0)) AS makerGrouponBalance " +
            "			FROM zdd_company_payment pay " +
            "			LEFT JOIN zdd_payment payment ON pay.pay_id = payment.id AND payment.source_table != 0 " +
            "			WHERE pay.compid = compid " +
            "           GROUP BY pay.compid) pay ON pay.compid = c.compid" +
            " WHERE c.state >= 4 " +
			" AND IF(ISNULL(#{name}) OR #{name}='',1=1, c.compname like CONCAT('%',#{name},'%') ) " +
			" GROUP BY c.compid " +
			" ORDER BY total DESC ")
    List<CompanyAccountBriefDto> getAccountBalanceList(@Param("name") String name);

    @Select("SELECT c.compid,c.compname,payment.pay_name,pay.balance " +
			" FROM zdd_company c  " +
			" LEFT JOIN zdd_company_payment pay ON c.compid = pay.compid AND pay.deleted = 0" +
			" LEFT JOIN zdd_payment payment ON pay.pay_id = payment.id " +
			" WHERE c.state >= 4  " +
			" AND IF(ISNULL(#{name}) OR #{name}='',1=1, c.compname like CONCAT('%',#{name},'%') ) " +
			" AND pay.balance != 0" +
			" GROUP BY c.compid,pay.pay_id" +
			" ORDER BY c.compid,pay.balance DESC")
    List<CompanyBalanceBriefDto> exportAccountBalanceList(@Param("name") String name);

	@Select(" select count(DISTINCT compid)as compNum,years,business_manager_id,merchandiserName,nickname,   " +
			"    sum(zddAmount)as zddAmount,   " +
			"    sum(crowdsourceAmount)as crowdsourceAmount,   " +
			"    sum(entrustZddAmount)as entrustZddAmount,   " +
			"    sum(entrustCrowdsourceAmount)as entrustCrowdsourceAmount,   " +
			"    sum(groupZddAmount)as groupZddAmount,   " +
			"    sum(groupCrowdsourceAmount)as groupCrowdsourceAmount,   " +
			"    sum(taxerAmount)as taxerAmount   " +
			"    from (   " +
			"     SELECT mak.bindcompid as compid,bm.business_manager_id,bm.business_manager as merchandiserName,bm.nickname,    " +
			"       DATE_FORMAT(i.createtime,'%Y-%m') as years,   " +
			"       0 AS zddAmount ,    " +
			"       0 AS crowdsourceAmount,    " +
			"       0 AS entrustZddAmount,    " +
			"       0 AS entrustCrowdsourceAmount,   " +
			"       0 AS groupZddAmount,    " +
			"       0 AS groupCrowdsourceAmount,   " +
			"      sum(i.amount) AS taxerAmount   " +
			"       FROM zdd_taxer_invoice i   " +
			"      LEFT JOIN zdd_maker_studio stu on stu.userid = i.userid   " +
			"      LEFT JOIN zdd_maker mak on mak.userid = stu.userid   " +
			"       LEFT JOIN zdd_company_account ca ON ca.compid = mak.bindcompid    " +
			"       LEFT JOIN zdd_business_manager bm ON  ca.business_manager_id = bm.business_manager_id     " +
			"       LEFT JOIN zdd_company com on com.compid = mak.bindcompid    " +
			"       WHERE bm.business_manager_id is not null     " +
			"      AND mak.maker_type = 1 and stu.taxer = 1   " +
			"      AND i.deleted = 0 AND mak.maker_type = 1 and stu.taxer = 1   " +
			"       AND YEAR(i.createtime) = #{year}     " +
			"       AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1   )   " +
			"     AND IF(ISNULL(#{name}) OR #{name} = '' , ' 1 = 1 ' ,   CONCAT(bm.business_manager,bm.nickname) LIKE '%${name}%' )     " +
			"      GROUP BY ca.business_manager_id,mak.bindcompid   " +
			"    union all    " +
			"     SELECT i.compid,bm.business_manager_id,bm.business_manager as merchandiserName,bm.nickname,    " +
			"       DATE_FORMAT(i.createtime,'%Y-%m') as years,     " +
			"       sum(if( i.project_type = 1 , i.invoice_amount , 0 )) AS zddAmount ,      " +
			"       sum(if( i.project_type = 2 , i.invoice_amount , 0 )) AS crowdsourceAmount,      " +
			"       sum(if( i.project_type = 5 , i.invoice_amount , 0 )) AS entrustZddAmount,   " +
			"       sum(if( i.project_type = 6 , i.invoice_amount , 0 )) AS entrustCrowdsourceAmount,   " +
			"       sum(if( i.project_type = 7 , i.invoice_amount , 0 )) AS groupZddAmount,   " +
			"       sum(if( i.project_type = 8 , i.invoice_amount , 0 )) AS groupCrowdsourceAmount,   " +
			"      0 AS taxerAmount   " +
			"       FROM zdd_invoice i      " +
			"       LEFT JOIN zdd_company_account ca ON ca.compid = i.compid     " +
			"       LEFT JOIN zdd_business_manager bm ON  ca.business_manager_id = bm.business_manager_id     " +
			"       LEFT JOIN zdd_company com on com.compid=i.compid     " +
			"       WHERE bm.business_manager_id is not null      " +
			"       AND YEAR(i.createtime) = #{year}     " +
			"       AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1   )   " +
			"     AND IF(ISNULL(#{name}) OR #{name} = '' , ' 1 = 1 ' ,   CONCAT(bm.business_manager,bm.nickname) LIKE '%${name}%' )   " +
			"      GROUP BY ca.business_manager_id,i.compid   " +
			"   )total GROUP BY business_manager_id,years")
    List<CorporateAmountDto> statisticCorporateAmount(@Param("year") int year, @Param("month") int month, @Param("name") String name);

	/**
	 * 导出具体某个专业顾问月度业绩-明细
	 * @return
	 */
	@Select("select base.compid,base.compname,base.business_manager_id,base.merchandiserName,base.nickname," +
			" DATE_FORMAT(base.auth_pass_time,'%Y-%m-%d') as authPassTime,total.years, " +
			"    sum(zddAmount)as zddAmount,    " +
			"    sum(crowdsourceAmount)as crowdsourceAmount,    " +
			"    sum(entrustZddAmount)as entrustZddAmount,    " +
			"    sum(entrustCrowdsourceAmount)as entrustCrowdsourceAmount,    " +
			"    sum(groupZddAmount)as groupZddAmount,    " +
			"    sum(groupCrowdsourceAmount)as groupCrowdsourceAmount,    " +
			"    sum(taxerAmount)as taxerAmount    " +
			"    from " +
			"    (" +
			"		SELECT com.compid,com.compname,bm.business_manager_id,bm.business_manager AS merchandiserName, bm.nickname,ca.auth_pass_time " +
			"       FROM zdd_company_account ca  " +
			"       LEFT JOIN zdd_business_manager bm ON bm.business_manager_id = ca.business_manager_id  " +
			"       LEFT JOIN zdd_company com ON com.compid = ca.compid " +
			"       WHERE ca.business_manager_id = #{merchandiserId} " +
			"    ) base " +
			"    join (    " +
			"     SELECT mak.bindcompid as compid,ca.business_manager_id,DATE_FORMAT(i.createtime,'%Y-%m') as years,  " +
			"       0 AS zddAmount ,0 AS crowdsourceAmount, " +
			"       0 AS entrustZddAmount,0 AS entrustCrowdsourceAmount, " +
			"       0 AS groupZddAmount,0 AS groupCrowdsourceAmount,sum(i.amount) AS taxerAmount " +
			"      FROM zdd_taxer_invoice i " +
			"      LEFT JOIN zdd_maker_studio stu on stu.userid = i.userid  " +
			"      LEFT JOIN zdd_maker mak on mak.userid = i.userid  " +
			"      LEFT JOIN zdd_company_account ca ON ca.compid = mak.bindcompid  " +
			"      WHERE ca.business_manager_id is not null " +
			"      AND mak.maker_type = 1 and stu.taxer = 1 " +
			"      AND i.deleted = 0 AND mak.maker_type = 1 and stu.taxer = 1" +
			"      AND YEAR(i.createtime) = #{year}" +
			"      AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1 ) " +
			" 	   AND ca.business_manager_id = #{merchandiserId}" +
			"      GROUP BY ca.business_manager_id,mak.bindcompid " +
			"    UNION ALL " +
			"     SELECT i.compid,ca.business_manager_id,DATE_FORMAT(i.createtime,'%Y-%m') as years, " +
			"      sum(if( i.project_type = 1 , i.invoice_amount , 0 )) AS zddAmount , " +
			"      sum(if( i.project_type = 2 , i.invoice_amount , 0 )) AS crowdsourceAmount, " +
			"      sum(if( i.project_type = 5 , i.invoice_amount , 0 )) AS entrustZddAmount, " +
			"      sum(if( i.project_type = 6 , i.invoice_amount , 0 )) AS entrustCrowdsourceAmount, " +
			"      sum(if( i.project_type = 7 , i.invoice_amount , 0 )) AS groupZddAmount,    " +
			"      sum(if( i.project_type = 8 , i.invoice_amount , 0 )) AS groupCrowdsourceAmount, " +
			"      0 AS taxerAmount " +
			"      FROM zdd_invoice i  " +
			"      LEFT JOIN zdd_company_account ca ON ca.compid = i.compid " +
			"      WHERE ca.business_manager_id is not null  " +
			"      AND YEAR(i.createtime) = #{year} " +
			"      AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1)  " +
			" 	   AND ca.business_manager_id = #{merchandiserId} " +
			"      GROUP BY ca.business_manager_id,i.compid " +
			" )total on base.compid = total.compid " +
			" GROUP BY base.compid,base.business_manager_id ")
	List<ExportStatisticCorporateAmountDto> exportStatisticCorporateAmountDetail(@Param("year") int year, @Param("month") int month,int merchandiserId);

	@Select(" SELECT  bm.business_manager,com.compname,i.createtime ,DATE_FORMAT(i.createtime,'%Y-%m') as years, " +
			" sum(if( i.project_type = 1 , i.invoice_amount ,  0  )) AS zddAmount , " +
			" sum(if( i.project_type = 2 , i.invoice_amount , 0 )) AS zzbAmount " +
			" FROM zdd_invoice i " +
			" LEFT JOIN zdd_company_account ca ON ca.compid = i.compid " +
			" LEFT JOIN zdd_business_manager bm ON  ca.business_manager_id = bm.business_manager_id " +
			" left join zdd_company com on com.compid=i.compid " +
			" WHERE " +
			" IF( ISNULL(#{name}) OR #{name} = '' ,1 = 1,   CONCAT(bm.business_manager,bm.nickname) LIKE '%${name}%') " +
			" and  YEAR(i.createtime) = #{year} " +
			" AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1) " +
			" GROUP BY ca.business_manager_id,i.compid")
	List<CorporateAmountDtoInfo> statisticCorporateAmountInfo(@Param("year") int year, @Param("month") int month, @Param("name") String name);

	// 冻结金额，余额-amount,冻结余额+amount
	@Update("update zdd_company_payment cp " +
			" set cp.balance = cp.balance - #{amount}, cp.frozen_balance = cp.frozen_balance + #{amount}" +
			" where cp.compid =  #{compid}" +
			" and cp.id = #{compPayId} ")
	boolean frozenBalance(int compid, Integer compPayId, BigDecimal amount);

	// 解除冻结金额，余额+amount,冻结余额-amount
	@Update("update zdd_company_payment cp " +
			" set cp.balance = cp.balance + #{amount}, cp.frozen_balance = cp.frozen_balance - #{amount}" +
			" where cp.compid =  #{compid}" +
			" and cp.id = #{compPayId} ")
	boolean removeFrozenBalance(int compid, Integer compPayId, BigDecimal amount);

	// 取消冻结金额，冻结余额-amount
	@Update("update zdd_company_payment cp " +
			" set cp.frozen_balance = cp.frozen_balance - #{amount}" +
			" where cp.compid =  #{compid}" +
			" and cp.id = #{compPayId} ")
	boolean cancelFrozenBalance(int compid, Integer compPayId, BigDecimal amount);

	// 充值金额，余额+amount
	@Update("update zdd_company_payment cp " +
			" set cp.balance = cp.balance + #{amount}" +
			" where cp.compid =  #{compid}" +
			" and cp.id = #{compPayId} ")
	boolean rechargeBalance(int compid, Integer compPayId, BigDecimal amount);

//	 解除冻结金额，余额+amount,冻结余额-amount
//	 取消冻结金额，冻结余额-amount
//	 充值金额，余额+amount
//	@Update("update zdd_company_payment cp " +
//			" set cp.balance = cp.balance - #{amount}, cp.frozen_balance = cp.frozen_balance + #{amount}" +
//			" where cp.compid =  #{compid}" +
//			" and cp.id = #{compPayId} ")
//	boolean applyTransferBalance(int compid, Integer compPayId, BigDecimal amount);
//
//	@Update(" update zdd_company_account ca " +
//			" set ca.maker_groupon_balance = ca.maker_groupon_balance + #{amount}, ca.maker_groupon_frozen_balance = ca.maker_groupon_frozen_balance - #{amount} " +
//			" where ca.compid = #{compid} and ca.maker_groupon_frozen_balance >= #{amount} " +
//			" ")
//	boolean confirmTransferMakerGroupon(Integer compid, BigDecimal amount);
//
//	@Update(" update zdd_company_account ca " +
//			" set maker_groupon_balance = maker_groupon_balance - #{companyPayAmount} " +
//			" where ca.compid = #{compid} ")
//	boolean deductMakerGrouponBalance(int compid, BigDecimal companyPayAmount);

	@Select(" select count(DISTINCT compid)as compNum,years,merchandiser_id,merchandiserName,nickname," +
			"	sum(zddAmount)as zddAmount," +
			"	sum(crowdsourceAmount)as crowdsourceAmount," +
			"	sum(entrustZddAmount)as entrustZddAmount," +
			"	sum(entrustCrowdsourceAmount)as entrustCrowdsourceAmount," +
			"	sum(groupZddAmount)as groupZddAmount," +
			"	sum(groupCrowdsourceAmount)as groupCrowdsourceAmount," +
			"	sum(taxerAmount)as taxerAmount" +
			"	from (" +
			"		SELECT ca.merchandiser_id,mak.bindcompid as compid,bm.business_manager_id,bm.business_manager as merchandiserName,bm.nickname, " +
			"  		DATE_FORMAT(i.createtime,'%Y-%m') as years," +
			"   	0 AS zddAmount , " +
			"   	0 AS crowdsourceAmount, " +
			"   	0 AS entrustZddAmount, " +
			"   	0 AS entrustCrowdsourceAmount," +
			"   	0 AS groupZddAmount, " +
			"   	0 AS groupCrowdsourceAmount," +
			" 		sum(i.amount) AS taxerAmount" +
			"   	FROM zdd_taxer_invoice i" +
			" 		LEFT JOIN zdd_maker_studio stu on stu.userid = i.userid" +
			" 		LEFT JOIN zdd_maker mak on mak.userid = stu.userid" +
			"   	LEFT JOIN zdd_company_account ca ON ca.compid = mak.bindcompid " +
			"   	LEFT JOIN zdd_business_manager bm ON  ca.merchandiser_id = bm.business_manager_id  " +
			"   	LEFT JOIN zdd_company com on com.compid = mak.bindcompid " +
			"   	WHERE bm.business_manager_id is not null  " +
			" 		AND mak.maker_type = 1 and stu.taxer = 1" +
			" 		AND i.deleted = 0 AND mak.maker_type = 1 and stu.taxer = 1" +
			"   	AND YEAR(i.createtime) = #{year}  " +
			"   	AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1) " +
			" 		GROUP BY ca.merchandiser_id,mak.bindcompid" +
			"	union all " +
			"		SELECT ca.merchandiser_id,i.compid,bm.business_manager_id,bm.business_manager as merchandiserName,bm.nickname, " +
			"  		DATE_FORMAT(i.createtime,'%Y-%m') as years,  " +
			"   	sum(if( i.project_type = 1 , i.invoice_amount , 0 )) AS zddAmount ,   " +
			"   	sum(if( i.project_type = 2 , i.invoice_amount , 0 )) AS crowdsourceAmount,   " +
			"   	sum(if( i.project_type = 5 , i.invoice_amount , 0 )) AS entrustZddAmount," +
			"   	sum(if( i.project_type = 6 , i.invoice_amount , 0 )) AS entrustCrowdsourceAmount," +
			"   	sum(if( i.project_type = 7 , i.invoice_amount , 0 )) AS groupZddAmount," +
			"   	sum(if( i.project_type = 8 , i.invoice_amount , 0 )) AS groupCrowdsourceAmount," +
			" 		0 AS taxerAmount" +
			"   	FROM zdd_invoice i   " +
			"   	LEFT JOIN zdd_company_account ca ON ca.compid = i.compid  " +
			"   	LEFT JOIN zdd_business_manager bm ON  ca.merchandiser_id = bm.business_manager_id  " +
			"   	LEFT JOIN zdd_company com on com.compid=i.compid  " +
			"   	WHERE bm.business_manager_id is not null   " +
			"   	AND YEAR(i.createtime) = #{year}  " +
			"   	AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1) " +
			" 		GROUP BY ca.merchandiser_id,i.compid" +
			")total GROUP BY merchandiser_id,years")
	List<CompanyMerchandiserDto> statisticMerchandiser(@Param("year") int year, @Param("month") int month);

	@Select("SELECT ccp.id AS partyId,ccp.company_name AS partyName, " +
			" SUM(IF(tmp.pay_type_sn in ('ZS','PA','BTL'),balance,0)) AS bankBalance, " +
			" SUM(IF(tmp.pay_type_sn in ('ZFBB2C','ZFBWTJD'),balance,0)) AS makerGrouponBalance " +
			" from zdd_crowdsource_company_party ccp" +
			" left join (" +
			"  SELECT com.compid,com.compname,pay.balance,pay.frozen_balance,payment.pay_type_sn,payment.party_id " +
			"  FROM zdd_company com" +
			"  JOIN zdd_company_account acc on acc.compid = com.compid " +
			"  JOIN zdd_company_payment pay on pay.compid = com.compid " +
			"  JOIN zdd_payment payment on pay.pay_id = payment.id " +
			"  WHERE com.state >= 4 " +
			" AND acc.whether_subject != 1" +
			" ) tmp on ccp.id = tmp.party_id" +
			" GROUP BY ccp.id ")
	List<PartyAmountDto> queryCompanyAmountByParty();

	@Select("SELECT com.compid,com.compname,pay.balance,pay.frozen_balance,payment.pay_type_sn,payment.pay_name,payment.party_id " +
			"  FROM zdd_company com" +
			"  JOIN zdd_company_account acc on acc.compid = com.compid " +
			"  JOIN zdd_company_payment pay on pay.compid = com.compid " +
			"  JOIN zdd_payment payment on pay.pay_id = payment.id " +
			"  WHERE com.state >= 4 and pay.balance != 0 " +
			" AND com.compname like '%${name}%' " +
			" AND IF(#{partyId}=0, 1=1 , payment.party_id = #{partyId} )" +
			" AND IF(#{whetherSubject}=2 , 1=1 , acc.whether_subject = #{whetherSubject} ) " +
			" ORDER BY pay.balance desc ")
	List<CompanyBalanceBriefDto> queryCompanyAmountByPartyList(String name, Integer partyId,int whetherSubject);

	@Select("SELECT pro.projectfinish,pro.realname,ma.auth_submit_time,pro.compname,pro.username,pro.projectdealamount " +
			"FROM zdd_company_project pro  " +
			"JOIN (SELECT MAX(pro.projectid) AS projectid,userid FROM zdd_company_project pro GROUP BY userid)tmp ON pro.projectid = tmp.projectid " +
			"JOIN zdd_maker_account ma ON ma.userid = pro.userid " +
			"WHERE pro.projectfinish <= SUBDATE(NOW(),INTERVAL 6 MONTH) " +
			"AND ma.auth_submit_time <= SUBDATE(NOW(),INTERVAL 6 MONTH) " +
			"AND ma.maker_label IS NULL  " +
			"AND ma.realname_auth = 5  " +
			"UNION ALL " +
			"SELECT ti.createtime,ma.realname,ma.auth_submit_time,ti.company_name,ti.username,ti.amount " +
			"FROM zdd_taxer_invoice ti " +
			"JOIN (SELECT MAX(ti.invoiceid)AS invoiceid FROM zdd_taxer_invoice ti GROUP BY ti.userid)zti ON ti.invoiceid = zti.invoiceid " +
			"JOIN zdd_maker_account ma ON ma.userid = ti.userid " +
			"WHERE ti.createtime <= SUBDATE(NOW(),INTERVAL 6 MONTH) " +
			"AND ma.auth_submit_time <= SUBDATE(NOW(),INTERVAL 6 MONTH) " +
			"AND ma.maker_label IS NULL  " +
			"AND ma.realname_auth = 5  " +
			"UNION ALL " +
			"SELECT cpi.createtime,cpi.realname,ma.auth_submit_time,cp.compname,cpi.username,cpi.project_amount  " +
			"FROM zdd_crowdsource_project_item cpi  " +
			"JOIN ( SELECT MAX(cpi.id) AS id FROM zdd_crowdsource_project_item cpi GROUP BY cpi.userid )zcpi ON cpi.id = zcpi.id JOIN zdd_maker_account ma ON ma.userid = cpi.userid  " +
			"JOIN zdd_crowdsource_project cp ON cp.projectid = cpi.projectid  " +
			"WHERE cpi.createtime <= SUBDATE(NOW(),INTERVAL 6 MONTH)  " +
			"AND ma.auth_submit_time <= SUBDATE(NOW(),INTERVAL 6 MONTH)  " +
			"AND ma.maker_label IS NULL  " +
			"AND ma.realname_auth = 5 " +
			"GROUP BY ma.userid")
	List<NonTradingMakerDto> getNonTradingMaker();

	@Select("		SELECT com.compid AS companyid,com.compname AS companyName," +
			"		DATE_FORMAT(i.createtime,'%Y-%m') as years," +
			"		bm.business_manager as merchandiserName," +
			"   	0 AS zddAmount , " +
			"   	0 AS crowdsourceAmount, " +
			"   	0 AS entrustZddAmount, " +
			"   	0 AS entrustCrowdsourceAmount," +
			"   	0 AS groupZddAmount," +
			"   	0 AS groupCrowdsourceAmount," +
			" 		sum(i.amount) AS taxerAmount" +
			"   	FROM zdd_taxer_invoice i" +
			" 		LEFT JOIN zdd_maker_studio stu on stu.userid = i.userid" +
			" 		LEFT JOIN zdd_maker mak on mak.userid = stu.userid" +
			"   	LEFT JOIN zdd_company_account ca ON ca.compid = mak.bindcompid " +
			"   	LEFT JOIN zdd_business_manager bm ON  ca.merchandiser_id = bm.business_manager_id  " +
			"   	LEFT JOIN zdd_company com on com.compid = mak.bindcompid " +
			"   	WHERE bm.business_manager_id is not null  " +
			" 		AND mak.maker_type = 1 and stu.taxer = 1" +
			" 		AND i.deleted = 0 AND mak.maker_type = 1 and stu.taxer = 1" +
			"   	AND YEAR(i.createtime) = #{year}  " +
			"   	AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1) " +
			"		AND IF( #{merchandiserId} = 0 ,1=1,bm.business_manager_id = #{merchandiserId} )" +
			" 		GROUP BY ca.merchandiser_id,mak.bindcompid ")
	List<GetAmountDto> getTaxerAmount(@Param("year") int year, @Param("month") int month,
												@Param("merchandiserId") int merchandiserId);

	@Select("		SELECT i.compid AS companyid,com.compname AS companyName, " +
			"  		DATE_FORMAT(i.createtime,'%Y-%m') as years, " +
			"		bm.business_manager as merchandiserName, " +
			" 		0 AS taxerAmount, " +
			"   	sum(if( i.project_type = 1 , i.invoice_amount , 0 )) AS zddAmount ,   " +
			"   	sum(if( i.project_type = 2 , i.invoice_amount , 0 )) AS crowdsourceAmount,   " +
			"   	sum(if( i.project_type = 5 , i.invoice_amount , 0 )) AS entrustZddAmount," +
			"   	sum(if( i.project_type = 6 , i.invoice_amount , 0 )) AS entrustCrowdsourceAmount," +
			"   	sum(if( i.project_type = 7 , i.invoice_amount , 0 )) AS groupZddAmount," +
			"   	sum(if( i.project_type = 8 , i.invoice_amount , 0 )) AS groupCrowdsourceAmount" +
			"   	FROM zdd_invoice i   " +
			"   	LEFT JOIN zdd_company_account ca ON ca.compid = i.compid  " +
			"   	LEFT JOIN zdd_business_manager bm ON  ca.merchandiser_id = bm.business_manager_id  " +
			"   	LEFT JOIN zdd_company com on com.compid=i.compid  " +
			"   	WHERE bm.business_manager_id is not null   " +
			"   	AND YEAR(i.createtime) = #{year}  " +
			"   	AND IF( #{month} > 0 , MONTH(i.createtime) = #{month} , 1 = 1) " +
			"		AND IF( #{merchandiserId} = 0,1=1,bm.business_manager_id = #{merchandiserId} )" +
			"		AND IF( #{projectType} = 0,1=1,i.project_type = #{projectType}) " +
			" 		GROUP BY ca.merchandiser_id,i.compid" )
	List<GetAmountDto> getAmount(@Param("year") int year, @Param("month") int month,
						   @Param("merchandiserId") int merchandiserId,@Param("projectType") int projectType);

	@Select("		SELECT bm.business_manager AS merchandiserName," +
			"		com.compname AS companyName," +
			"  		DATE_FORMAT(#{years},'%Y-%m') as years, " +
			"		com.compid AS companyid, " +
			"   	0 AS zddAmount , " +
			"   	0 AS crowdsourceAmount, " +
			"   	0 AS entrustZddAmount, " +
			"   	0 AS entrustCrowdsourceAmount," +
			"   	0 AS groupZddAmount," +
			"   	0 AS groupCrowdsourceAmount," +
			" 		0 AS taxerAmount" +
			"		FROM zdd_company_account ca " +
			"		LEFT JOIN zdd_business_manager bm ON bm.business_manager_id = ca.merchandiser_id " +
			"		LEFT JOIN zdd_company com ON com.compid = ca.compid " +
			"		WHERE ca.merchandiser_id = #{merchandiserId} " +
			"		AND DATE_FORMAT(ca.auth_pass_time,'%Y-%m') <= DATE_FORMAT(#{years},'%Y-%m') ")
	List<GetAmountDto> getEnterprise(@Param("years") Date years, @Param("merchandiserId") int merchandiserId);

	@Update(" update zdd_company_account set marketing_cooperative = #{state} where compid in(${compids})")
	int switchMarketingCooperative(String compids, int state);

	@Select("select c.compid,c.compname,c.intel_crowdsource,c.base_tax_rate,c.base_tax_rate_odd,c.open_zdd,c.open_odd_job," +
			" c.project_service_rate,c.base_tax_rate,c.low_tax_rate,c.high_tax_rate,ca.maker_groupon," +
			" ct.gen_low_service_rate,ct.gen_high_service_rate,ct.gen_high_tax_rate,ct.spe_service_rate,ct.spe_tax_rate," +
			" ct.yearly_high_tax_rate,ct.yearly_high_service_rate,ct.yearly_high_2_tax_rate,ct.yearly_high_2_service_rate," +
			" ct.yearly_high_3_tax_rate,ct.yearly_high_3_service_rate,ct.fixed_service,ct.fixed_service_rate,ct.company_tax_flag" +
			" from zdd_company c " +
			" left join zdd_company_account ca on ca.compid = c.compid " +
			" left join zdd_company_tax ct on c.compid = ct.compid" +
			" where c.state = 4 " +
			" and (ca.account_link_id = #{accountLinkId} or c.compid = #{compid}) ")
	List<CompanyLabelDto> getCompanyAccountList(int compid, Integer accountLinkId);
}
